package board;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;


public class Board {

	//첨부 파일
	int fileSize = 10*1024*1024;
	String uploadPath = "C:\\kjm_1403\\web1403\\WebContent\\MyHome\\board\\upload";
	String fileEncoding = "utf-8";


	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String db_user = "hr";
	String db_pwd = "hr";

	Connection conn;
	BoardVo vo;
	
	
	
	// 페이지분리 관련된 필드
	int listSize = 3; // 한페이지에 보여질 행수
	int blockSize = 2; // 보여질 페이지 이동 번호 수

	int totSize = 0; // 검색된 데이터 총 갯수
	int totPage = 1; // 전체 페이지 수
	int totBlock = 1; // 전체 블럭수
	
	int nowPage = 1; // 현제 페이지
	int nowBlock = 1; // 현재 블럭
	
	int startNo = 1; // 목록의 시작 위치
	int endNo = 3; // 목록의 끝 위치
	int startPage = 1; // 현재 블럭의 시작 페이지번호
	int endPage = 2; //현재블럭의 끝페이지 번호
	
	
	

	public Board(){
		try {
			// 1) 드라이버 로딩
			Class.forName(driver).newInstance();

			// 2) Connection 객체 생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);

			// 3) PreparedStatement 작성
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void computePage(){
		totPage = (int)Math.ceil(totSize*1.0/listSize);
		totBlock = (int)Math.ceil(totPage*1.0/blockSize);
		
		nowBlock = (int)Math.ceil(nowPage*1.0/blockSize);
		
		endPage = nowBlock*blockSize;
		startPage = endPage - blockSize+1;
		if(totPage < endPage){
			endPage = totPage;
		}
		endNo = nowPage * listSize;
		startNo = endNo - listSize + 1;
		if(totSize < endNo) endNo = totSize;
	}

	public Vector<BoardVo> list(){

		Vector<BoardVo> v = new Vector<>();  
		String sql = "";
		PreparedStatement ps = null;
		ResultSet rs = null;
		// 전체 페이지 수
		try{
			
		sql = "select count(serial) cnt from board where (mid like ? or subject like ? or content like ?) and part=? order by grp desc, deep asc";	
		
		ps = conn.prepareStatement(sql);
		ps.setString(1, "%"+ vo.getFind() +"%");
		ps.setString(2, "%"+ vo.getFind() +"%");
		ps.setString(3, "%"+ vo.getFind() +"%");
		ps.setString(4, vo.getPart());
		rs = ps.executeQuery();
		rs.next();
		/*System.out.println("cnt" + rs.getInt("cnt"));*/
		
		totSize = rs.getInt("cnt");
		computePage();
		
		sql = 	"select * from("
				+ "	  select rownum no, a.* from("
				+ "      select * from board where (mid like ? or subject like ? "
				+ "      or content like ?) and part=? "
				+ "      order by grp desc, deep asc)a"
				+ ") where no between ? and ?";
		
	
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+ vo.getFind() +"%");
			ps.setString(2, "%"+ vo.getFind() +"%");
			ps.setString(3, "%"+ vo.getFind() +"%");
			ps.setString(4, vo.getPart());
			ps.setInt(5, startNo);
			ps.setInt(6, endNo);
			rs = ps.executeQuery();
			
			

			while(rs.next()){

				BoardVo dv = new BoardVo();
				dv.setSerial(rs.getInt("serial"));
				dv.setMid(rs.getString("mid"));
				dv.setSubject(rs.getString("subject"));
				dv.setMdate(rs.getDate("mdate"));
				dv.setHit(rs.getInt("hit"));

				v.add(dv);
			}

			rs.close();
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return v;
	}
	



	public boolean insert(){
		boolean flag=false;
		try{
			MultipartRequest mr = new MultipartRequest(
					vo.getRequest(),
					uploadPath,
					fileSize, 
					fileEncoding, 
					new DefaultFileRenamePolicy()
					);

			String part = mr.getParameter("part");
			String mid = mr.getParameter("mid");
			String pwd = mr.getParameter("pwd");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");

			String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");

			String sql = "insert all into board values(brd_seq.nextval, ?, ?, sysdate, ?, ?, 1, brd_seq.currval, brd_seq.currval, ?)";
			/*+"into brdfiles values(brdfile_seq.nextval, brd_seq.currval, ?)"
						+"select * from dual";*/
			if(sysfile != null){
				sql += "into brdfiles values(brdfile_seq.nextval, brd_seq.currval, ?)";
			}
			sql += "select * from dual";

			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, part);
			ps.setString(2, mid);
			ps.setString(3, pwd);
			ps.setString(4, subject);
			ps.setString(5, content);

			if(sysfile != null){
				ps.setString(6, sysfile);
			}


			int cnt = ps.executeUpdate();
			if(cnt > 0) flag=true;

			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	public boolean repinsert(){
		boolean flag=false;
		try{
			MultipartRequest mr = new MultipartRequest(
					vo.getRequest(),
					uploadPath,
					fileSize, 
					fileEncoding, 
					new DefaultFileRenamePolicy()
					);

			String part = mr.getParameter("part");
			String mid = mr.getParameter("mid");
			String pwd = mr.getParameter("pwd");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");

			String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");

			int grp = Integer.parseInt(mr.getParameter("grp"));
			String deep = mr.getParameter("deep");


			String sql = "insert all into board values(brd_seq.nextval, ?, ?, sysdate, ?, ?, 1, ?, ? || brd_seq.currval, ?)";

			if(sysfile != null){
				sql += "into brdfiles values(brdfile_seq.nextval, brd_seq.currval, ?)";
			}

			sql += "select * from dual";

			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, part);
			ps.setString(2, mid);
			ps.setString(3, pwd);
			ps.setString(4, subject);
			ps.setInt(5, grp);
			ps.setString(6, deep + "-");
			ps.setString(7, content);

			if(sysfile != null){
				ps.setString(8, sysfile);
			}

			int cnt = ps.executeUpdate();
			if(cnt > 0) flag=true;

			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}

	public BoardVo view(){
		String sql = null;
		BoardVo bvo = null;
		PreparedStatement ps;


		try{
			sql = "update board set hit = hit+1 where serial=" + vo.getSerial();

			ps = conn.prepareStatement(sql);
			ps.executeUpdate();


			sql = "select b.*,f.filename from board b left outer join brdfiles f on b.serial=f.fileid where b.serial = ?";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());


			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				bvo = new BoardVo();

				bvo.setSerial(rs.getInt("serial"));
				bvo.setMid(rs.getString("mid"));
				bvo.setPwd(rs.getString("pwd"));
				bvo.setSubject(rs.getString("subject"));
				bvo.setContent(rs.getString("content"));
				bvo.setMdate(rs.getDate("mdate"));
				bvo.setFilename(rs.getString("filename"));
				bvo.setGrp(rs.getInt("grp"));
				bvo.setDeep(rs.getString("deep"));
				bvo.setHit(rs.getInt("hit"));

			}
			rs.close();
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return bvo;
	}


	public boolean delete(){
		boolean flag = false;
		String sql = "delete from board where serial=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());

			int cnt = ps.executeUpdate();
			if(cnt >0) 	flag = true;

			File file = new File(uploadPath + "/" + vo.getFilename());
			if(file.exists()){
				file.delete();
			}else{
				//고민상담
			}

			ps.close();
			conn.close();
		}catch(Exception e){

		}
		return flag;
	}
	public boolean modify(){
		boolean flag=false;
		try{
			MultipartRequest mr = 
					new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding, new DefaultFileRenamePolicy());
			String mid = mr.getParameter("mid");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String pwd = mr.getParameter("pwd");
			String filename = mr.getParameter("filename");
			String sysfile = mr.getFilesystemName("newfilename");
			
			int serial = Integer.parseInt(mr.getParameter("serial"));
			String fileDelFlag = mr.getParameter("fileDelFlag");

			PreparedStatement ps = null;
			String sql = null;
			int cnt = 0; // 처리결과

			//자동 커밋을 중지.
			conn.setAutoCommit(false);
			
			//기존 첨부된 파일을 삭제
			if(fileDelFlag.equals("del")){
				FileDel(filename, serial);
			}
			

			//새로운 첨부파일이 존재하지 않으면(내용만 수정된 경우)
			sql = "update board set subject=?, content=? where pwd=? and serial=?";

			ps = conn.prepareStatement(sql);
			ps.setString(1, subject);
			ps.setString(2, content);
			ps.setString(3, pwd);
			ps.setInt(4, serial);
			cnt = ps.executeUpdate();
			
			
			// 새로운 첨부파일이 생성된 경우(내용+첨부파일이 모두 수정된 경우)
			if(sysfile != null && sysfile != ""){
				sql = "insert into brdfiles(serial, fileid, filename) values(brdfile_seq.nextval, ?, ?)";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, serial);
				ps.setString(2, sysfile);

				cnt = ps.executeUpdate();
				
				if(cnt>0){ // insert가 정상적으로 이루어졌을경우 위의 작업내용을 commit
					conn.commit();
					flag=true;
				}else{  // 정상적으로 이루어지지않앗을경우 작업 rollback
					conn.rollback();
				}
			}
			if(cnt>0){
				conn.commit();
				flag=true;
			}else{
				conn.rollback();
			}

			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	// 기존첨부된 파일을 DB에서 삭제하고 파일이 존재할경우 실제파일도 삭제
	public void FileDel(String oldfile, int serial){
		String sql = "delete from brdfiles where fileid=? and filename=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, serial);
			ps.setString(2, oldfile);
			ps.executeUpdate();

			File file = new File(uploadPath + "/" + oldfile);
			if(file.exists()) file.delete();

			
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}


/*	public boolean fileinsert(){
		boolean flag=false;
		try{
			MultipartRequest mr = new MultipartRequest(
					vo.getRequest(),
					uploadPath,
					fileSize, 
					fileEncoding, 
					new DefaultFileRenamePolicy()
					);

			String fileid = mr.getParameter("fileid");

			String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");


			String sql = "insert into databank values(brdfile_seq.nextval,?,?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getMid());
			ps.setString(2, sysfile);


			int cnt = ps.executeUpdate();
			if(cnt>0) flag=true;

			
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}

		return flag;
	}*/
	public BoardVo getVo() {
		return vo;
	}
	public void setVo(BoardVo vo) {
		this.vo = vo;
	}

	
	public String getUploadPath() {
		return uploadPath;
	}
	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}
	

}
